﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

namespace syc.Juece.Report
{
    public partial class 月主要产品产量表 : System.Web.UI.Page
    {
        protected double[] 计划量 = new double[12];//计划量
        protected double[] 实际量 = new double[12];//实际量
        protected double[] 本月止累计量 = new double[12];//本月止累计量
        protected double[] 完成率 = new double[12];//完成率

        protected string 品名;
        protected string 钢级;
        protected string 单位;

        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {                
                this.Year0.SelectedValue = DateTime.Today.Year.ToString();
            }
        }
        protected double 月计划量(int month,string 品名)
        {
            double 计划量 = 0;
            SqlConnection Conn = new SqlConnection();
            Conn.ConnectionString = ConfigurationManager.ConnectionStrings["SCMConnectionString1"].ConnectionString;
            Conn.Open();
            SqlCommand Cmd = new SqlCommand();
            Cmd.Connection = Conn;
            if (品名 == "套管")
            {
                string sql = @"select (一分厂产品2+二分厂产品2) from 生产计划 where 月份=" + month + "and 年度=" + this.Year0.SelectedValue;
                Cmd.CommandText = sql;
            }
            if (品名 == "油管")
            {
                string sql = @"select (二分厂产品1+五分厂产品1) from 生产计划 where 月份=" + month + "and 年度=" + this.Year0.SelectedValue;
                Cmd.CommandText = sql;
            }
            if (品名 == "油管接箍")
            {
                string sql = @"select (三分厂产品1) from 生产计划 where 月份=" + month + "and 年度=" + this.Year0.SelectedValue;
                Cmd.CommandText = sql;
            }
            if (品名 == "套管接箍")
            {
                string sql = @"select (三分厂产品2) from 生产计划 where 月份=" + month + "and 年度=" + this.Year0.SelectedValue;
                Cmd.CommandText = sql;
            }
            if (品名 == "螺纹保护器")
            {
                string sql = @"select 四分厂产品2 from 生产计划 where 月份=" + month + "and 年度=" + this.Year0.SelectedValue;
                Cmd.CommandText = sql;
            }           
            SqlDataReader Reader = Cmd.ExecuteReader();
            if (Reader.Read())
            {
                if (Reader.IsDBNull(0))
                {
                    计划量 = 0;
                }
                else
                {
                    计划量 = Reader.GetDouble(0);
                }
            }
            Reader.Close();
            Conn.Close();
            Reader.Dispose();
            Cmd.Dispose();
            Conn.Dispose();
            return 计划量;
        }
        //月实际量
        protected double 月实际量(int month,string 品名)
        {
            double 实际量 = 0;
            SqlConnection Conn = new SqlConnection();
            Conn.ConnectionString = ConfigurationManager.ConnectionStrings["SCMConnectionString1"].ConnectionString;
            Conn.Open();
            SqlCommand Cmd = new SqlCommand();
            Cmd.Connection = Conn;
            if (品名 == "套管")
            {
                string sql = @"
                 declare @yc smalldatetime
                 declare @ym smalldatetime
                 declare @一分厂入库量 float
                 declare @二分厂入库量 float
                 select @yc=起始日期,@ym=截止日期 from 生产月设置
                 where 分厂=1 and 年度='" + this.Year0.SelectedValue + "' and 月份='" + month + "' select @一分厂入库量 =isnull(sum(入库重量),0)/1000 from 生产进度 where 分厂ID=1 and 产品类别ID=2 and 日期 between @yc and @ym select @yc=起始日期,@ym=截止日期 from 生产月设置 where 分厂=2 and 年度='" + this.Year0.SelectedValue + "' and 月份='" + month + "' select @二分厂入库量=isnull(sum(入库重量),0)/1000 from 生产进度 where 分厂ID=2 and 产品类别ID=2 and 日期 between @yc and @ym select @一分厂入库量+@二分厂入库量";

                Cmd.CommandText = sql;
            }
            if (品名 == "油管")
            {
                string sql = @"
                declare @yc smalldatetime
                declare @ym smalldatetime
                declare @二分厂入库量 float
                declare @五分厂入库量 float
                select @yc=起始日期,@ym=截止日期 from 生产月设置
                where 分厂=2 and 年度='" + this.Year0.SelectedValue + "' and 月份='" + month + "' select @二分厂入库量=isnull(sum(入库重量),0)/1000 from 生产进度 where 分厂ID=2 and 产品类别ID=1 and 日期 between @yc and @ym select @yc=起始日期,@ym=截止日期 from 生产月设置 where 分厂=5 and 年度='" + this.Year0.SelectedValue + "' and 月份='" + month + "' select @五分厂入库量=isnull(sum(入库重量),0)/1000 from 生产进度 where 分厂ID=5 and 产品类别ID=1 and 日期 between @yc and @ym select @二分厂入库量+@五分厂入库量";

                Cmd.CommandText = sql;
            }
            
            if (品名 == "油管接箍")
            {
                string sql = @"
                declare @yc smalldatetime
                declare @ym smalldatetime
                declare @yjiegu float                
                select @yc=起始日期,@ym=截止日期 from 生产月设置
                where 分厂=3 and 年度='" + this.Year0.SelectedValue + "' and 月份='" + month + "' select @yjiegu=isnull(sum(磷化支数),0) from 生产进度3 where 产品类别ID=1 and 日期 between @yc and @ym select (@yjiegu)";

                Cmd.CommandText = sql;
            }
            if (品名 == "套管接箍")
            {
                string sql = @"
                declare @yc smalldatetime
                declare @ym smalldatetime                
                declare @tjiegu float
                select @yc=起始日期,@ym=截止日期 from 生产月设置
                where 分厂=3 and 年度='" + this.Year0.SelectedValue + "' and 月份='" + month + "' select @tjiegu=isnull(sum(磷化支数),0) from 生产进度3 where 产品类别ID=2 and 日期 between @yc and @ym select (@tjiegu)";

                Cmd.CommandText = sql;
            }
            if (品名 == "螺纹保护器")
            {
                string sql = @"
                declare @yc smalldatetime
                declare @ym smalldatetime
                select @yc=起始日期,@ym=截止日期 from 生产月设置
                where 分厂=4 and 年度='" + this.Year0.SelectedValue + "' and 月份='" + month + "' select (isnull(sum(入库),0)) from 生产进度4 where 产品类别ID=2 and 日期 between @yc and @ym";

                Cmd.CommandText = sql;
            }         
            SqlDataReader Reader = Cmd.ExecuteReader();
            if (品名 == "螺纹保护器")
            {
                if (Reader.Read())
                {
                    if (Reader.IsDBNull(0))
                    {
                        实际量 = 0;
                    }
                    else
                    {
                        实际量 = Reader.GetInt32(0);
                    }
                }
            }
            else
            {
                if (Reader.Read())
                {
                    if (Reader.IsDBNull(0))
                    {
                        实际量 = 0;
                    }
                    else
                    {
                        实际量 = Reader.GetDouble(0);
                    }
                }
            }
            Reader.Close();
            Conn.Close();
            Reader.Dispose();
            Cmd.Dispose();
            Conn.Dispose();
            return 实际量;
        }
        //本月止完成量
        protected double 本月止完成量(int month, string 品名)
        {
            double 本月止完成量 = 0;
            SqlConnection Conn = new SqlConnection();
            Conn.ConnectionString = ConfigurationManager.ConnectionStrings["SCMConnectionString1"].ConnectionString;
            Conn.Open();
            SqlCommand Cmd = new SqlCommand();
            Cmd.Connection = Conn;
            if (品名 == "套管")
            {
                string sql = @"
                declare @nc smalldatetime
                declare @ym smalldatetime
                declare @一分厂入库量 float
                declare @二分厂入库量 float
                select @nc=起始日期 from 生产月设置 where 年度='" + this.Year0.SelectedValue + "' and 月份='1'and 分厂=1 select @ym=截止日期 from 生产月设置 where 年度='" + this.Year0.SelectedValue + "' and 月份='" + month + "' and 分厂=1 select @一分厂入库量=isnull(sum(入库重量),0) from 生产进度 where 分厂ID=1 and 产品类别ID=2 and 日期 between @nc and @ym    select @nc=起始日期 from 生产月设置 where 年度='" + this.Year0.SelectedValue + "' and 月份='" + month + "'and 分厂=2 select @ym=截止日期 from 生产月设置 where 年度='" + this.Year0.SelectedValue + "' and 月份='" + month + "' and 分厂=2 select @二分厂入库量=isnull(sum(入库重量),0) from 生产进度 where 分厂ID=2 and 产品类别ID=2 and 日期 between @nc and @ym select @一分厂入库量+@二分厂入库量";

                Cmd.CommandText = sql;
            }
            if (品名 == "油管")
            {
                string sql = @"
                declare @nc smalldatetime
                declare @ym smalldatetime
                declare @二分厂入库量 float
                declare @五分厂入库量 float
                select  @nc=起始日期 from 生产月设置 where 年度='" + this.Year0.SelectedValue + "' and 月份='1'and 分厂=2 select @ym=截止日期 from 生产月设置 where 年度='" + this.Year0.SelectedValue + "' and 月份='" + month + "' and 分厂=2 select @二分厂入库量=isnull(sum(入库重量),0) from 生产进度 where 分厂ID=2 and 产品类别ID=1 and 日期 between @nc and @ym  select @nc=起始日期 from 生产月设置 where 年度='" + this.Year0.SelectedValue + "' and 月份='1'and 分厂=5 select @ym=截止日期 from 生产月设置 where 年度='" + this.Year0.SelectedValue + "' and 月份='" + month + "' and 分厂=5 select @五分厂入库量=isnull(sum(入库重量),0) from 生产进度 where 分厂ID=5 and 产品类别ID=1 and 日期 between @nc and @ym select @二分厂入库量+@五分厂入库量";

                Cmd.CommandText = sql;
            }

            if (品名 == "油管接箍")
            {
                string sql = @"
                declare @nc smalldatetime
                declare @ym smalldatetime
                declare @yjiegu float
                declare @tjiegu float
               select @nc=起始日期 from 生产月设置 where 年度='" + this.Year0.SelectedValue + "' and 月份='1' and 分厂=3 select @ym=截止日期 from 生产月设置 where 年度='" + this.Year0.SelectedValue + "' and 月份='" + month + "' and 分厂=3 select @yjiegu=isnull(sum(磷化支数),0) from 生产进度3 where  产品类别ID=1 and 日期 between @nc and @ym select @yjiegu";

                Cmd.CommandText = sql;
            }
            if (品名 == "套管接箍")
            {
                string sql = @"
                declare @nc smalldatetime
                declare @ym smalldatetime
                declare @yjiegu float
                declare @tjiegu float
                select @nc=起始日期 from 生产月设置 where 年度='" + this.Year0.SelectedValue + "' and 月份='1' and 分厂=3 select @ym=截止日期 from 生产月设置 where 年度='" + this.Year0.SelectedValue + "' and 月份='" + month + "' and 分厂=3 select @tjiegu=isnull(sum(磷化支数),0) from 生产进度3 where  产品类别ID=2 and 日期 between @nc and @ym select @tjiegu";

                Cmd.CommandText = sql;
            }
            if (品名 == "螺纹保护器")
            {
                string sql = @"
                declare @nc smalldatetime
                declare @ym smalldatetime
                select @nc=起始日期 from 生产月设置 where 年度='" + this.Year0.SelectedValue + "' and 月份='1'and 分厂=4 select @ym=截止日期 from 生产月设置 where 年度='" + this.Year0.SelectedValue + "' and 月份='" + month + "' and 分厂=4 select isnull(sum(入库),0) from 生产进度4 where 产品类别ID=2 and 日期 between @nc and @ym";

                Cmd.CommandText = sql;
            }
            SqlDataReader Reader = Cmd.ExecuteReader();
            if (品名 == "螺纹保护器")
            {
                if (Reader.Read())
                {
                    if (Reader.IsDBNull(0))
                    {
                        本月止完成量 = 0;
                    }
                    else
                    {
                        本月止完成量 = Reader.GetInt32(0);
                    }
                }
            }
            else
            {
                if (Reader.Read())
                {
                    if (Reader.IsDBNull(0))
                    {
                        本月止完成量 = 0;
                    }
                    else
                    {
                        本月止完成量 = Reader.GetDouble(0);
                    }
                }
            }
            Reader.Close();
            Conn.Close();
            Reader.Dispose();
            Cmd.Dispose();
            Conn.Dispose();
            return 本月止完成量;
        }

        protected void Button_Query_Click(object sender, EventArgs e)
        {
            品名 = this.PinMing.SelectedItem.Text;
            if (品名 == "油管" || 品名 == "套管")
            {
                钢级 = "J55";
            }
            else
            {
                钢级 = "-";
            }
            if (品名 == "油管" || 品名 == "套管")
            {
                单位 = "吨";
            }
            else if (品名 == "油管接箍"||品名 =="套管接箍")
            {
                单位 = "支";
            }
            else
            {
                单位 = "套";
            }
            //计划量
            for (int i = 0; i < 12; i++)
            {
                计划量[i] = 月计划量(i + 1,this.PinMing.SelectedItem.Text);
            }
            //实际量
            for (int i = 0; i < 12; i++)
            {
                实际量[i] = 月实际量(i + 1,this.PinMing.SelectedItem.Text);
            }
            //本月止完成量
            for (int i = 0; i < 12; i++)
            {
                本月止累计量[i] = 本月止完成量(i + 1, this.PinMing.SelectedItem.Text);
            }
            //完成率            
            for (int i = 0; i < 12; i++)
            {
                if (计划量[i] == 0)
                {
                    完成率[i] = 0;
                }
                else
                {
                    完成率[i] = 实际量[i] / 计划量[i];
                }
            }
        }
     

    }
}
